package com.cdshangpai.utils.db;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.cdshangpai.photoapp.ui.PriceFilter;
import com.cdshangpai.utils.db.beans.Case;

import java.util.ArrayList;
import java.util.List;

//案例数据库操作
public class CaseDb {
    SQLiteDatabase db=DatabaseHelper.getDb();
    String tablename="t_case";

    public CaseDb(){

    }

    //增加
    public long insert(Case c)
    {
        long id=db.insert(tablename,null,c.getValues());

        return id;
    }

    //修改
    public void update(Case c)
    {

        db.update(tablename,c.getValues(),"id=?",new String[]{c.id.toString()});

    }

    //删除
    public void delete(Integer id)
    {
        DatabaseHelper.getDb().delete(tablename,"id=?",new String[]{id.toString()});
    }

    //清除数据
    public void clean()
    {
        String sql="delete from "+tablename;
        DatabaseHelper.getDb().execSQL(sql);
    }

    public Case queryById(Integer id)
    {
        Case cs=null;
        String sql="select * from "+tablename+" where id="+id;
        Cursor c=db.rawQuery(sql,null);
        c.moveToFirst();
        if(!c.isAfterLast())
        {
            cs=cToCase(c);
        }
        c.close();
        return cs;
    }

    //查询
    public List<Case> query(String[] area, String[] city, String[] style, String[] color, String[] price/*,PriceFilter[] price*/)
    {
        List<Case> result=new ArrayList<>();
        String sql="";
        ArrayList<String> wheres=new ArrayList<>();
//        if(!TextUtils.isEmpty(area))
//        {
//            sql=" area=? and ";
//            wheres.add(area);
//        }
        if(area!=null&&area.length>0)
        {
            StringBuilder sb=new StringBuilder();
            sb.append(" (");
            for(int i=0;i<area.length;i++)
            {
                sb.append(" area  = ?");
                if(i+1!=area.length)
                {
                    sb.append(" or ");
                }
                wheres.add(area[i]);
            }
            sb.append(") and ");
            sql+=sb.toString();
        }
        if(city!=null&&city.length>0)
        {
            StringBuilder sb=new StringBuilder();
            sb.append(" (");
            for(int i=0;i<city.length;i++)
            {
                sb.append(" city  like ?");
                if(i+1!=city.length)
                {
                    sb.append(" or ");
                }
                String like="%"+city[i]+"%";
                wheres.add(like);
            }
            sb.append(") and ");
            sql+=sb.toString();
        }
        if(style!=null&&style.length>0)
        {
            StringBuilder sb=new StringBuilder();
            sb.append(" (");
            for(int i=0;i<style.length;i++)
            {
                sb.append(" `style`  = ?");
                if(i+1!=style.length)
                {
                    sb.append(" or ");
                }
                wheres.add(style[i]);
            }
            sb.append(") and ");
            sql+=sb.toString();
        }
        if(color!=null&&color.length>0)
        {
            StringBuilder sb=new StringBuilder();
            sb.append(" (");
            for(int i=0;i<color.length;i++)
            {
                sb.append(" `color`  = ?");
                if(i+1!=color.length)
                {
                    sb.append(" or ");
                }
                wheres.add(color[i]);
            }
            sb.append(") and ");
            sql+=sb.toString();
        }
        if(price!=null&&price.length>0)
        {
            StringBuilder sb=new StringBuilder();
            sb.append(" (");
            for(int i=0;i<price.length;i++)
            {
                sb.append(" `price`  = ?");
                if(i+1!=price.length)
                {
                    sb.append(" or ");
                }
                wheres.add(price[i]);
            }
            sb.append(") and ");
            sql+=sb.toString();
        }
        /*if(price!=null&&price.length>0)
        {
            StringBuilder sb=new StringBuilder();
            sb.append(" (");
            for(int i=0;i<price.length;i++)
            {
                sb.append("(");
                PriceFilter priceFilter=price[i];
                Integer beginPrice=priceFilter.getFromPrice();
                Integer toPrice=priceFilter.getToPrice();
                if(beginPrice!=null)
                {
                    sb.append(" price>=? ");
                    wheres.add(beginPrice.toString());
                }
                if(toPrice!=null)
                {
                    if(beginPrice!=null)
                    {
                        sb.append(" and ");
                    }
                    sb.append(" price<=? ");
                    wheres.add(toPrice.toString());
                }
                sb.append(")");
                if(i+1!=price.length)
                {
                    sb.append(" or ");
                }

            }
            sb.append(") and ");
            sql+=sb.toString();
        }*/

        Cursor c=null;
        if(wheres.size()>0)
        {
            Integer lastpos=sql.lastIndexOf("and");
            sql=sql.substring(0,lastpos);
            sql="select * from "+tablename+" where "+sql;
            sql+=" order by `index` desc ";
            c=DatabaseHelper.getDb().rawQuery(sql,wheres.toArray(new String[wheres.size()]));
        }
        else{
            sql="select * from "+tablename;
            sql+=" order by `index` desc ";
            c=DatabaseHelper.getDb().rawQuery(sql,null);
        }
        c.moveToFirst();
        while (!c.isAfterLast())
        {
            Case item=cToCase(c);
            result.add(item);

            c.moveToNext();
        }
        c.close();
        return result;
    }

    public List<Case> query_old(String area,String[] city,String style,String color,String price,Integer beginPrice,Integer toPrice)
    {
        List<Case> result=new ArrayList<>();
        String sql="";
        ArrayList<String> wheres=new ArrayList<>();
        if(!TextUtils.isEmpty(area))
        {
            sql=" area=? and ";
            wheres.add(area);
        }
        if(city!=null&&city.length>0)
        {
            StringBuilder sb=new StringBuilder();
            sb.append(" city in(");
            for(int i=0;i<city.length;i++)
            {
                sb.append("?");
                if(i+1!=city.length)
                {
                    sb.append(",");
                }
                wheres.add(city[i]);
            }
            sb.append(") and ");
            sql+=sb.toString();
        }
        if(!TextUtils.isEmpty(style))
        {
            sql+=" style=? and ";
            wheres.add(style);
        }
        if(!TextUtils.isEmpty(color))
        {
            sql+=" color=? and ";
            wheres.add(color);
        }
        if(beginPrice!=null)
        {
            sql+=" price>=? and ";
            wheres.add(beginPrice.toString());
        }
        if(toPrice!=null)
        {
            sql+=" price<=? and ";
            wheres.add(toPrice.toString());
        }
        Cursor c=null;
        if(wheres.size()>0)
        {
            Integer lastpos=sql.lastIndexOf("and");
            sql=sql.substring(0,lastpos);
            sql="select * from "+tablename+" where "+sql;
            c=DatabaseHelper.getDb().rawQuery(sql,wheres.toArray(new String[wheres.size()]));
        }
        else{
            sql="select * from "+tablename;
            c=DatabaseHelper.getDb().rawQuery(sql,null);
        }
        c.moveToFirst();
        while (!c.isAfterLast())
        {
            Case item=cToCase(c);
            result.add(item);

            c.moveToNext();
        }
        c.close();
        return result;
    }

    public List<Case> queryByKeyword(String keyword){
        List<Case> result=new ArrayList<>();
        ArrayList<String> wheres=new ArrayList<>();
        String like="%"+keyword+"%";
        String sql="select * from "+tablename+" where title like ? or content like ?  or hotal like ?"+" order by `index` desc ";
        for(int i=0;i<3;i++)
        {
            wheres.add(like);
        }
        Cursor c=null;
        c=DatabaseHelper.getDb().rawQuery(sql,wheres.toArray(new String[wheres.size()]));
        c.moveToFirst();
        while (!c.isAfterLast())
        {
            Case item=cToCase(c);
            result.add(item);

            c.moveToNext();
        }
        c.close();
        return result;
    }

    public List<Case> queryByIds(List<Integer> ids)
    {
        List<Case> result=new ArrayList<>();
        StringBuilder sqlBuilder=new StringBuilder();
        ArrayList<String> wheres=new ArrayList<>();
        sqlBuilder.append("select * from ");
        sqlBuilder.append(tablename);
        if(ids.size()>0);
        {
            sqlBuilder.append(" where id in(");
            for(int i=0;i<ids.size();i++)
            {
                sqlBuilder.append("?");
                wheres.add(ids.get(i).toString());
                if(i<ids.size()-1)
                {
                    sqlBuilder.append(",");
                }
            }
            sqlBuilder.append(")");
        }
        Cursor cursor=null;
        String sql=sqlBuilder.toString();
        sql+=" order by `index` desc ";
        if(wheres.size()>0)
        {
            String[] params=wheres.toArray(new String[wheres.size()]);
            cursor=db.rawQuery(sql, params);
        }
        else
        {
            cursor=db.rawQuery(sql,null);
        }
        cursor.moveToFirst();
        while (!cursor.isAfterLast())
        {
            Case cas=cToCase(cursor);
            result.add(cas);
            cursor.moveToNext();
        }
        return result;
    }

    private Case cToCase(Cursor c)
    {
        Case item=new Case();
        item.id=c.getInt(c.getColumnIndex("id"));
        item.index=c.getLong(c.getColumnIndex("index"));
        item.title=c.getString(c.getColumnIndex("title"));
        item.content=c.getString(c.getColumnIndex("content"));
        item.photos=c.getString(c.getColumnIndex("photos"));
        item.area=c.getString(c.getColumnIndex("area"));
        item.city=c.getString(c.getColumnIndex("city"));
        item.color=c.getString(c.getColumnIndex("color"));
        item.style=c.getString(c.getColumnIndex("style"));
        item.price=c.getString(c.getColumnIndex("price"));
        item.titleimg=c.getString(c.getColumnIndex("titleimg"));
        item.version=c.getString(c.getColumnIndex("version"));
        item.hotalid=c.getInt(c.getColumnIndex("hotalid"));
        item.hotal=c.getString(c.getColumnIndex("hotal"));
        return item;
    }

    public void test()
    {
        clean();
        Case test=queryById(1);

        Case c=new Case();
        c.title="title";
        c.content="content";
        c.photos="photos";
        c.titleimg="titleimg";
        c.area="area";
        c.city="city";
        c.color="color";
        c.style="style";
        c.price="";
        c.version="version";

        Long id=insert(c);

        c=queryById(id.intValue());

        /*String[] citys=new String [] {"city","成都"};
        List<Case> queryResult=query("area",citys,"style","color",99,102);

        c.title="title update";
        update(c);

        queryResult=query("area",citys,"style","color",99,102);

        delete(id.intValue());

        queryResult=query("area",citys,"style","color",99,102);*/



    }
}
